import time
import mysql.connector
from mysql.connector import Error
from datetime import datetime
from typing import Dict, List
from concurrent.futures import ThreadPoolExecutor
from .product_models import AlignedPetProduct
from .knowledge_graph import KnowledgeGraph

class DataScheduler:
    def __init__(self, db_config: Dict):
        self.db_config = db_config
        self.knowledge_graph = KnowledgeGraph(db_config)

    def _create_connection(self):
        """创建数据库连接"""
        try:
            return mysql.connector.connect(
                host=self.db_config['host'],
                port=self.db_config['port'],
                user=self.db_config['user'],
                password=self.db_config['password'],
                database=self.db_config['database']
            )
        except Error as e:
            print(f"Error while connecting to MySQL: {e}")
            return None

    def _get_alignment_data(self) -> List[AlignedPetProduct]:
        """从数据库中获取并验证对齐数据"""
        query = f"SELECT * FROM {self.db_config['alignment_table']}"
        connection = self._create_connection()
        if not connection:
            return []
            
        try:
            cursor = connection.cursor(dictionary=True)
            cursor.execute(query)
            raw_data = cursor.fetchall()
            
            # 验证数据并转换为模型实例
            validated_data = []
            for item in raw_data:
                try:
                    product = AlignedPetProduct(**item)
                    validated_data.append(product)
                except Exception as e:
                    print(f"数据验证失败: {e}")
            return validated_data
        finally:
            connection.close()

    def _save_alignment_result(self, data: List[AlignedPetProduct]):
        """将对齐结果保存到数据库"""
        if not data:
            return
            
        connection = self._create_connection()
        if not connection:
            return
            
        try:
            cursor = connection.cursor()
            
            # 清空现有数据
            cursor.execute(f"TRUNCATE TABLE {self.db_config['alignment_table']}")
            
            # 插入新数据
            query = f"""
                INSERT INTO {self.db_config['alignment_table']} 
                (product_id, aligned_product_id, similarity_score,
                product_name, brand, category, sub_category, 
                target_species, target_breeds, weight, size,
                ingredients, material, text_embedding, image_embedding,
                image_hash, supplier_code, batch_number, price,
                stock_status, source_db, alignment_score, last_aligned_time)
                VALUES (
                    %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
                    %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
                )
            """
            
            # 准备批量插入数据
            insert_data = [
                (
                    item.product_id, item.aligned_product_id, item.similarity_score,
                    item.product_name, item.brand, item.category, item.sub_category,
                    json.dumps(item.specs.target_species), 
                    json.dumps(item.specs.target_breeds) if item.specs.target_breeds else None,
                    item.specs.weight, item.specs.size,
                    json.dumps(item.composition.ingredients) if item.composition and item.composition.ingredients else None,
                    json.dumps(item.composition.material) if item.composition and item.composition.material else None,
                    json.dumps(item.text_embedding),
                    json.dumps(item.image_embedding) if item.image_embedding else None,
                    item.image_hash, item.supplier_code, item.batch_number,
                    item.price, item.stock_status, item.source_db,
                    item.alignment_score, item.last_aligned_time
                )
                for item in data
            ]
            
            cursor.executemany(query, insert_data)
            connection.commit()
        finally:
            connection.close()

    def run_alignment_task(self):
        """执行数据对齐任务"""
        print(f"[{datetime.now()}] Starting data alignment task...")
        start_time = time.time()
        
        data = self._get_alignment_data()
        self._save_alignment_result(data)
        self._update_knowledge_graph(data)
        
        elapsed_time = time.time() - start_time
        print(f"[{datetime.now()}] Data alignment completed. Took {elapsed_time:.2f} seconds")

    def _update_knowledge_graph(self, products: List[AlignedPetProduct]):
        """根据对齐结果更新知识图谱"""
        for product in products:
            # 创建知识图谱条目
            knowledge = {
                'pet_type': ', '.join(product.specs.target_species),
                'question': f"关于{product.product_name}的信息",
                'answer': f"品牌: {product.brand}, 分类: {product.category}, 子类: {product.sub_category}"
            }
            self.knowledge_graph.save_knowledge(knowledge)

    def start_scheduler(self, interval: int = 3600):
        """启动定时任务调度"""
        with ThreadPoolExecutor() as executor:
            while True:
                executor.submit(self.run_alignment_task)
                time.sleep(interval)

if __name__ == "__main__":
    config = {
        'host': 'rm-2zea4lxfy2974xc9xso.mysql.rds.aliyuncs.com',
        'port': 3306,
        'user': 'ry',
        'password': 'Qwer1111',
        'database': 'mingxiangzhu_mall',
        'alignment_table': 'yx_store_product_alignment'
    }
    
    scheduler = DataScheduler(config)
    
    # 单次执行
    scheduler.run_alignment_task()
    
    # 定时执行（每小时一次）
    # scheduler.start_scheduler()
